package com.example.fileprovider;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.*;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
 * project:
 * author: wzq
 * date: 2014/7/2
 * description:
 */
public class Xls2Html implements Htmlable {

    public Xls2Html(String filePath) {
        this.filePath = filePath;
        //
        int index1 = filePath.lastIndexOf("/");
        String picturePath = filePath.substring(0, index1);//文件所处文件夹
        String prefix = filePath.substring(index1 + 1, filePath.lastIndexOf("."));//文件名，不包含路径以及后缀名
        picMgr = new OfficePicMgr(picturePath, prefix);
    }


    private String filePath;//文件路径
    private OfficePicMgr picMgr;
    private ByteArrayOutputStream output = new ByteArrayOutputStream();// 输出流

    @Override
    public InputStream getHtmlInputStream() {
        if(output == null) {
            return null;
        }

        byte[] bytes = output.toByteArray();
        try {
            output.write(bytes);
        } catch(IOException e) {
            e.printStackTrace();
        }
        return new ByteArrayInputStream(bytes);
    }

    /**
     * 拼接html语言的字符串
     */
    private StringBuffer lsb = new StringBuffer();

    private static final String A1 = "<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>";
    private static final String A2 = "<head><meta http-equiv=Content-Type content='text/html; charset=utf-8'><meta name=ProgId content=Excel.Sheet>";
    private static final String A3 = "<table width=\"100%\" style=\"border:1px solid #000;border-width:1px 0 0 1px;margin:2px 0 2px 0;border-collapse:collapse;\">";
    private static final String A4 = "<tr height=\"%s\" style=\"border:1px solid #000;border-width:0 1px 1px 0;margin:2px 0 2px 0;\">";
    private static final String A5 = "<td style=\"border:1px solid #000; border-width:0 1px 1px 0;margin:2px 0 2px 0; ";
    private static final String A6 = "<td>%s</td>";
    /**
     * 2013-5-15 下午2:47:34 chenhao添加此方法
     *
     * @throws Exception
     */
    @Override
    public boolean convertToHtml() {
        List<PicInfo> picInfos = new ArrayList<PicInfo>();
        lsb.append(A1);
        lsb.append(A2);
        HSSFSheet sheet = null;

        try {
            FileInputStream fis = new FileInputStream(filePath);
            HSSFWorkbook workbook = (HSSFWorkbook) WorkbookFactory.create(fis);
            for(int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
                if(workbook.getSheetAt(sheetIndex) == null) {
                    continue;
                }

                sheet = workbook.getSheetAt(sheetIndex);// 获得不为空的这个sheet
                picInfos.clear();
                if(sheet.getDrawingPatriarch() != null) {
                    List<HSSFShape> shapes = sheet.getDrawingPatriarch().getChildren();
                    for(HSSFShape shape : shapes) {
                        HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
                        if(shape instanceof HSSFPicture) {
                            HSSFPicture pic = (HSSFPicture) shape;
                            pic.getPictureData();

                            PicInfo info = new PicInfo();
                            info.setColNum(anchor.getCol1());
                            info.setRowNum(anchor.getRow1());
                            info.setSheetName(sheet.getSheetName());
                            info.setContent(pic.getPictureData().getData());
                            picInfos.add(info);
                            picMgr.savePicture(info);
                        }
                    }
                }
                int firstRowNum = sheet.getFirstRowNum(); // 第一行
                int lastRowNum = sheet.getLastRowNum(); // 最后一行

                int colNumMax = 0;// TODO: colNumMax
                for(int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
                    HSSFRow row = sheet.getRow(rowNum);
                    short lastCellNum = row.getLastCellNum(); // 该行的最后一个单元格
                    if(lastCellNum > colNumMax) {
                        colNumMax = lastCellNum;
                    }
                    LogMgr.d("最大colNum=" + colNumMax);
                }

                // 构造Table
                lsb.append(A3);
                for(int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
                    HSSFRow row = sheet.getRow(rowNum);
                    if(row == null) {
                        continue;
                    }

                    short firstCellNum = row.getFirstCellNum(); // 该行的第一个单元格
                    short lastCellNum = row.getLastCellNum(); // 该行的最后一个单元格
                    int height = (int) (row.getHeight() / 15.625); // 行的高度
                    lsb.append(String.format(A4, height + ""));

                    //
                    for(int cellNum = firstCellNum; cellNum < colNumMax; cellNum++) { // 循环该行的每一个单元格
                        HSSFCell cell = row.getCell(cellNum);
                        StringBuffer tdStyle = new StringBuffer(A5);

                        if(cell != null) {
                            HSSFCellStyle cellStyle = cell.getCellStyle();
                            short boldWeight = cellStyle.getFont(workbook).getBoldweight(); // 字体粗细
                            short fontHeight = (short) (cellStyle.getFont(workbook).getFontHeight() / 2); // 字体大小
                            tdStyle.append(" font-weight:" + boldWeight + "; ");
                            tdStyle.append(" font-size: " + fontHeight + "%;");
                            lsb.append(tdStyle + "\"");
                        }

                        // try to find the picture
                        String name = null;
                        LogMgr.d("rowNum:=" + rowNum + ",cellNum=" + cellNum);
                        String imgLink = "<img src=\"%s\"/>";
                        for(PicInfo picInfo : picInfos) {
                            if( picInfo.getRowNum() == rowNum
                                    && picInfo.getColNum() == cellNum
                                    &&  picInfo.getSheetName().equals(sheet.getSheetName())) {
                                name = picMgr.getPicSavePath(picInfo);
                            }

                            String content = "";//空串
                            if(name != null) { // 找到图片
                                LogMgr.d("找到图片:" + name);
                                content = String.format(imgLink, name);
                            } else { // 未找到图片
                                LogMgr.d("未找到图片:" + name);
                                content = (String) getCellValue(cell);
                            }
                            lsb.append(String.format(A6, content));
                        }
                    }
                    lsb.append("</tr>");
                }
            }
            LogMgr.d("try to write data to Stream");
            output.write(lsb.toString().getBytes());
            fis.close();
        } catch(FileNotFoundException e) {
            LogMgr.e("文件 " + filePath + " 没有找到!");
            e.printStackTrace();
            return false;
        } catch(IOException e) {
            LogMgr.e("文件 " + filePath + " 处理错误(" + e.getMessage() + ")!");
            return false;
        } catch(InvalidFormatException e) {
            e.printStackTrace();
            return false;
        } catch(Exception e) {
            e.printStackTrace();
            return false;
        }
        LogMgr.i("处理完毕OK");
        return true;
    }
    /**
     * 取得单元格的值
     *
     * @param cell
     *
     * @return
     *
     * @throws java.io.IOException
     */
    private Object getCellValue(HSSFCell cell) throws IOException {
        Object value = "";
        if(cell == null) {
            LogMgr.e("null cell");
            return value;
        }
        if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
            value = cell.getRichStringCellValue().toString();
        } else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
            if(HSSFDateUtil.isCellDateFormatted(cell)) {
                Date date = (Date) cell.getDateCellValue();
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                value = sdf.format(date);
            } else {
                double value_temp = (double) cell.getNumericCellValue();
                BigDecimal bd = new BigDecimal(value_temp);
                BigDecimal bd1 = bd.setScale(3, bd.ROUND_HALF_UP);
                value = bd1.doubleValue();
                DecimalFormat format = new DecimalFormat("#0.###");
                value = format.format(cell.getNumericCellValue());
            }
        }

        if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
            value = "";
        }
        return value;
    }
}
